﻿using INTEX.DataBase;
using INTEX.DataBase.Tools;
using ReflectionStudio.Core;
using ReflectionStudio.Core.Events;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
 
using System.IO;
using System.Linq;
using System.Windows;
using WPFExcelReport.Helpers;
namespace WPFExcelReport
{
    public class JLTVKHLReport : BaseReport
    {
        public ApplicationBase app
        {
            get
            {
                return ApplicationBase.Instance;
            }
        }
        public INTEXDBC INTEXDBC
        {
            get
            {
                return app.INTEXDBC;
            }
        }
        public INTEXBL INTEXBL
        {
            get
            {
                return app.INTEXBL;
            }
        }
        string sheetName = "";
        public bool IsOnAuditing = false;
        public bool IsSendForAuditing = false;
        string filePath = Path.Combine(PathHelper.ApplicationPath, @"Report\JLT\JLTVKPOS.xls");
        SETINTEXDB INTEXDB = new INTEX.DataBase.SETINTEXDB();
        Dictionary<string, int> qpplan = new Dictionary<string, int>();
        public bool IsApproved = false; 
        public string Shareroot = @"R:\";
        public JLTVKHLReport()
            : base()
        {
        } 
        public override void WriteExcelReport(string fileType)
        {
            Root = "C:\\IntexReport";
            bool IsFTP = true;
            try
            {
                if (exAgent == null)
                {
                    exAgent = new ExcelX.ExcelLib.ExcelAgent();
                }
                Helper.GetDate(ref date, ref year, ref month, ref day);
                strCompany = "JLT";
                strExcelFile = @"JLT\JLTVKPOS.xls";
                Standard = "VKHL";
                TemplatePath = Path.Combine(PathHelper.ApplicationPath, @"Report\JLT\JLTVKPOS.xls");
                destfolder = SetFolder(Root, "手织版通知单");

                #region==========准备程序===================
                DataRow row = dsSource.Tables["VKKOPF"].Rows[0];
                string fileid = row["VKKAUFKRS"].ToString().Trim() + "-" + Helper.GetSubRollNum(row["VKKAUFLFD"].ToString().Trim());
                DataRow dwData = INTEXDB.SDDAT.NewRow();
                dwData["SDSA"] = "0013";
                DataSet SResult = INTEXDBC.QuerySDDAT(dwData, 9999);
                dsSource.Merge(SResult);
                dsSource.Merge(INTEXDBC.QueryHRSTAMM(dwData, 3));

                dwData["SDSA"] = "0734";
                dsSource.Merge(INTEXDBC.QuerySDDAT(dwData, 9999));
                dwData["SDSA"] = "0477";
                dsSource.Merge(INTEXDBC.QuerySDDAT(dwData, 9999));
                dwData["SDSA"] = "0196";
                dsSource.Merge(INTEXDBC.QuerySDDAT(dwData, 9999));

                #endregion

                exAgent.CheckWorkBook(fileid);
                ServerShareFile(fileid, fileType);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                exAgent.Save();
                exAgent.Close();
                exAgent = null;
            }
        }
        private string CreateFile(string orderno)
        {
            string filename = "";
            try
            {
                string pwd = "";
                sheetName = orderno;
                exAgent.Open(TemplatePath);
                exAgent.Company = strCompany;
                exAgent.strExcelFile = strExcelFile;
                List<string> Standards = new List<string>();
                Standards.Add("VKHL");
                exAgent.CreateNewSheets(orderno + ".xls", Standards, pwd);
                exAgent.Save();
                filename = exAgent.FileName;
                var query = dsSource.Tables["VKPOS"].AsEnumerable()
                    .Select(row => new
                    {
                        VKPART = row.Field<string>("VKPART")
                    }).Distinct();
                foreach (var row in query)
                {
                    string vkpart = (string)row.VKPART.Trim();
                    sheetName = vkpart;
                    exAgent.SetCurrentSheet("VKHL");
                    exAgent.CopySheet(sheetName);
                    exAgent.SetCurrentSheet(vkpart);
                    FillInExcelData(columns, vkpart, 0, 0);
                }
                exAgent.DeleteSheet("VKHL");
                exAgent.SetCurrentSheet(sheetName);
                exAgent.Save();
                exAgent.Close();
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return filename;
        }
        public void SetTXTValue(string sgb,  int x, int y)
        {
            int length = 0;
            string s1 = "";
            string s2 = "";
            try
            {
                s1 = sgb;
                exAgent.SetCellValue(x, y, s1);
                x++;
                return;
                length = sgb.Length;
                if (length <= 50)
                {
                    s1 = sgb;
                    exAgent.SetCellValue(x, y, s1);
                    x++;
                    return;
                }
                s1 = sgb.Substring(0, 50);
                exAgent.SetCellValue(x, y, s1);
                x++;
                s2 = sgb.Substring(50, length - 50);
                exAgent.SetCellValue(x, y, s2);
                x++;
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        public void FillInExcelData(ArrayList columns,string vkpart, int xStart = 1, int yStart = 0)
        {
            string msg = "";
            string sdkey = "";
            try
            {
                if (!dsSource.Tables.Contains("VAART"))
                {
                    return;
                }
                if (!dsSource.Tables.Contains("VAFARBEN"))
                {
                    return;
                }
                string src = "";
                string sgb = ""; 
                DataTable tbVKKOPF = dsSource.Tables["VKKOPF"];
                DataTable tbVKP = dsSource.Tables["VKPOS"];
                string filter = "VKPART='" + vkpart + "'";
                DataRow[] rows = tbVKP.Select(filter);
                DataTable tbVKPOS = rows.CopyToDataTable();
                DataRow dwVKP = tbVKPOS.Rows[0];
                string KRS;
                string LFD;
                string POS;
                string TXTISIND; 
                int position = 0;
                string user = "";
                string text = "";
                DataRow dwVKKOPF = tbVKKOPF.Rows[0];
                DataTable tbVKPBEST = new DataTable(), tbVKK07 = new DataTable();
 
                string printdate, orderdate = ""; 
                int positions = 0; 
                DataTable tbVA07 = dsSource.Tables["VAART"];
                DataTable tbVA10 = dsSource.Tables["VAFARBEN"];
                DataRow dwVA07 = tbVA07.Rows[0];
                DataRow dwVA10 = null;
                double VKKQTY = 0;
                string meeh ="MTR";
                #region=============VKKOPF==================
                src = "手织版号：" + vkpart;
                exAgent.SetCellValue(3, 1, src);
                src = dwVKKOPF["VKKAUFKRS"].ToString() + "-" + Helper.GetSubRollNum(dwVKKOPF["VKKAUFLFD"].ToString());//销售单：
                exAgent.SetCellValue(3, 16, src);
                src = dwVKKOPF["VKKBESTNR"].ToString();// "合同号:" +
                exAgent.SetCellValue(3, 16, src);
         
                string VKKKUNDNR = dwVKKOPF["VKKKUNDNR"].ToString();
                src = System.Convert.ToInt64(VKKKUNDNR).ToString()+"#";//"客户编号：" +
                //exAgent.SetCellValue(4, 5, dwVKP["KDKUNDNA"].ToString());
                exAgent.SetCellValue(4, 5, GetGB2312Value(dwVKP["KDKNAME"].ToString()));
                exAgent.SetCellValue(4, 16,GetGB2312Value(dwVKP["KDNAME1"].ToString()));
                exAgent.SetCellValue(5, 5, dwVKP["VKPKOLL"].ToString());

                src = dwVKKOPF["VKKNEUBEN"].ToString();//跟单员:" +
                //src = dwVKKOPF["VKKSACHBE"].ToString();
                src = Helper.GetHRName(dsSource, src);
                exAgent.SetCellValue(7, 35, src);
                user = src;
                string year = dwVKKOPF["VKKNEUDAT"].ToString().Substring(0, 4);
                string month = dwVKKOPF["VKKNEUDAT"].ToString().Substring(4, 2);
                string day = dwVKKOPF["VKKNEUDAT"].ToString().Substring(6, 2);
                orderdate = "来样日期：" + year + "/" + month + "/" + day;
          
                exAgent.SetCellValue(1,34, orderdate);

                year = DateTime.Now.Year.ToString();
                month = DateTime.Now.Month.ToString();
                day = DateTime.Now.Day.ToString();
            
                printdate = "打印日期：" + year + "/" + month + "/" + day;
                exAgent.SetCellValue(2, 34, printdate);

                src = Helpers.Helper.GetCompositionVA(dsSource, dwVA07);
                exAgent.SetCellValue(6, 16, src);
                string vbrt = dwVA07["VAAMEBRT"].ToString().Trim();

                src = dwVA07["VAAALTERNAT"].ToString().Trim();
                src = Helper.GetWeave(dsSource, src);
                exAgent.SetCellValue(6, 35, src);
                #region============组织规格VAD===================
                tbVKK07 = new DataTable();
                string koll = dwVKP["VKPKOLL"].ToString();
                string art = dwVKP["VKPART"].ToString();
                text = "";
                TXTISIND = koll + art.Trim();
                var qvad = from dwTemp in dsSource.Tables["ICC_TXTKON"].AsEnumerable()
                           where dwTemp["TXTIPIND"].ToString().Trim() == TXTISIND
                           select dwTemp;
                if (qvad.Count() > 0)
                {
                    tbVKK07 = qvad.CopyToDataTable();
                }
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        string ci = "TXT" + Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); src = GetGB2312Value(src).Trim();
                        if (src == "")
                            break;
                        text += src;
                    }
                }
                exAgent.SetCellValue(5, 16, text);
                #endregion

                src = dwVA07["VAAPRGR"].ToString();
                sdkey = "00" + Left(src + "                                           ", 21) + "V";
                src = INTEXDBC.GetSDDAT("0196", sdkey);
                exAgent.SetCellValue(6, 5, GetGB2312Value(src.Substring(1, 20)).Trim());
                #endregion
                text = "";
                #region============开发依据BEST===================
               tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString(); 
                #endregion
                #region============开发建议LIEF===================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "LIEF";
                var query1 = from dwTemp in dsSource.Tables["ICC_TXTKON"].AsEnumerable()
                             where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                             select dwTemp;
                if (query1.Count() > 0)
                {
                    tbVKK07 = query1.CopyToDataTable();
                }

                text = "";
                string light = "";
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    light =GetGB2312Value(dwTXTKON["TXT010"].ToString());
                    for (int ti = 2; ti < 40; ti++)
                    {
                        string ci = "TXT" + Helper.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd();
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                }
                if (light.Trim() != "")
                {
                    int idx = 0;
                    idx = light.IndexOf('/');
                    if (idx > 0)
                    {
                        exAgent.SetCellValue(7, 18, light.Substring(0,idx));
                        exAgent.SetCellValue(7, 25, light.Substring(idx+1));
                    }
                }
                exAgent.SetCellValue(24, 5, text);
                #endregion
                #region ===================研发部吗寄样意见=====================
                tbVKK07 = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "RECH";
                var query = from dwTemp in dsSource.Tables["ICC_TXTKON"].AsEnumerable()
                            where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                            select dwTemp;
                if (query.Count() > 0)
                {
                    tbVKK07 = query.CopyToDataTable();
                }

                text = "";
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        string ci = "TXT" + Helper.Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd();
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                }
                exAgent.SetCellValue(36, 5, text);
                
                #endregion

                #region============销售部寄养意见LVOR===================
                tbVKK07 = new DataTable(); 
               KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "LVOR";
                var query5 = from dwTemp in dsSource.Tables["ICC_TXTKON"].AsEnumerable()
                        where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                        select dwTemp;
               if (query5.Count() > 0)
                {
                    tbVKK07 = query5.CopyToDataTable();
                }
  
               text = "";
                foreach (DataRow dwTXTKON in tbVKK07.Rows)
                {
                    for (int ti = 1; ti < 40; ti++)
                    {
                        string ci = "TXT" + Right("00" + ti, 2) + "0";
                        src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                        if (sgb != "")
                        {
                            text += sgb.TrimEnd();
                        }
                        else
                        {
                            text += sgb.TrimEnd();
                            break;
                        }
                    }
                    exAgent.SetCellValue(43, 5, text);
                }
                #endregion

                #region============花型要求IVVT===================

                DataTable tbSTD = new DataTable();
                KRS = dwVKKOPF["VKKAUFKRS"].ToString();
                LFD = dwVKKOPF["VKKAUFLFD"].ToString();
                TXTISIND = KRS + LFD + "000" + "IVVT";
                var query7 = from dwTemp in dsSource.Tables["ICC_TXTKON"].AsEnumerable()
                             where dwTemp["TXTIPIND"].ToString().Contains(TXTISIND)
                             select dwTemp;
                if (query7.Count() > 0)
                {
                    tbSTD = query7.CopyToDataTable();
                }
                #endregion 
                xStart = 53;
                int x = xStart;
                string finish = "";
                int count = tbVKPOS.Rows.Count;
                DataRow dwKDART = null;
                string deldate = "";
                foreach (DataRow dwVKPOS in tbVKPOS.Rows)
                {
                    deldate=dwVKPOS["VKPLIEFTERM"].ToString();
                    if (deldate.Trim() != "0")
                    {
                        count--;
                        continue;
                    }
                    dwKDART = null;
                    positions++;
                    msg = "开始写入：" + position.ToString() + "行";
                    EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.StopProgress);
                    KRS = dwVKPOS["VKPAUFKRS"].ToString();
                    LFD = dwVKPOS["VKPAUFLFD"].ToString();
                    POS = dwVKPOS["VKPAUFPOS"].ToString();
                    src = dwVKPOS["VKPPOSTYP"].ToString();//"记号:" 
                    exAgent.SetCellValue(3, 35, src);
                    #region====================VKPOS=======================
                    POS = dwVKPOS["VKPAUFPOS"].ToString();
                    exAgent.SetCellValue(x, 1, System.Convert.ToInt64(POS));
                    src = dwVKPOS["VKPART"].ToString().TrimEnd()  ;
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(x, 3, sgb);
                    #endregion
                    #region================组织===================
                    dwVA07 = null;
                    var q07 = from DataRow row in tbVA07.AsEnumerable()
                              where row["VAAART"].ToString() == dwVKPOS["VKPART"].ToString()
                              select row;
                    foreach (DataRow row in q07)
                    {
                        dwVA07 = row;
                    }
                    dwVA10 = null;
                    var q10 = from DataRow row in tbVA10.AsEnumerable()
                              where row["VAFART"].ToString() == dwVKPOS["VKPART"].ToString()
                              && row["VAFFARBE"].ToString() == dwVKPOS["VKPFARBE"].ToString()
                              select row;
                    foreach (DataRow row in q10)
                    {
                        dwVA10 = row;
                    }
                    src = dwVA10["VAFBESCHICH"].ToString().Trim();
                    if (src.Trim() != "")
                    {
                        sdkey = "00" + Left(src + "                                           ", 21);
                        src = INTEXDBC.GetSDDAT("0275", sdkey);
                        src = GetGB2312Value(src.Substring(1, 48)).Trim();
                        int idx = 0;
                        idx = src.IndexOfAny(new char[] { '(', '（' });
                        if (idx > 0)
                            src = src.Substring(0, idx);
                    }
                    if(!finish.Contains(src+";"))
                        finish+=src+";";
                    exAgent.SetCellValue(7, 5,(finish+"***").Replace(";***",""));
                    exAgent.SetCellValue(x, 11, src);
                    exAgent.SetCellValue(x, 8, dwVKPOS["VKPFARBE"].ToString().TrimEnd() + Environment.NewLine + GetGB2312Value(dwVA10["VAFBEZ"].ToString().Trim()));
                    #endregion 
                    #region================客人花号/品名======================
                    src = dwVKPOS["VKPKDARTNR"].ToString().TrimEnd() + " " + dwVKPOS["VKPKDBEZ"].ToString().TrimEnd() + " " + dwVKPOS["VKPKDFARBNR"].ToString().TrimEnd();
                    var qkd = from DataRow row in dsSource.Tables["VAKDBEZ"].AsEnumerable()
                              where row["VAKBKOLL"].ToString() == dwVKPOS["VKPKOLL"].ToString()
                              && row["VAKBART"].ToString() == dwVKPOS["VKPART"].ToString()
                              && row["VAKBFARBE"].ToString() == dwVKPOS["VKPFARBE"].ToString()
                              select row;
                    foreach (DataRow rkd in qkd)
                    {
                        src = rkd["VAKBKDARTNR"].ToString().TrimEnd() + " " + rkd["VAKBKDARTBEZ"].ToString().TrimEnd() + " " + rkd["VAKBKDFARBNR"].ToString().TrimEnd();
                        dwKDART = rkd;
                    }
                    sgb = GetGB2312Value(src);
                    exAgent.SetCellValue(x + 1, 3, sgb);
                    #endregion
 
                    #region================数量===================
                    src = dwVKPOS["VKPMENGE1"].ToString();
                    string VKPMENGE1 = Divide(src, 100);
                    VKKQTY += System.Convert.ToDouble(VKPMENGE1);
                    meeh = dwVKPOS["VKPMEEH"].ToString();
                    exAgent.SetCellValue(x, 17, VKPMENGE1 + " "+meeh); 
                    #endregion

                    #region =================交期======================
                    exAgent.SetCellValue(x, 21, dwVKPOS["VKPLIEFTERM"].ToString());
                    deldate = "确认:            ";
                    if (dwVKPOS["VKPLIEFTERM"].ToString().Trim() == "0")
                    {
                        deldate = "";
                    }
                    else
                    {
                        deldate = dwVKPOS["VKPLIEFTERM"].ToString().Trim();
                    }
                    exAgent.SetCellValue(5, 35, deldate);
                    //船样 
                    #endregion 
                    #region =================标准====================
                    text = "";
                    string std = GetGB2312Value(dwVKPOS["VKPZUSATZ"].ToString().Trim());
                    foreach (DataRow dwTXTKON in tbSTD.Rows)
                    {
                        for (int ti = 1; ti < 40; ti++)
                        {
                            string ci = "TXT" + Right("00" + ti, 2) + "0";
                            src = dwTXTKON[ci].ToString(); sgb = GetGB2312Value(src).Trim();
                            text = sgb.TrimEnd();
                            if (text.Trim() == "")
                                break;
                            int idxd = text.IndexOf(".");
                            if (idxd > 0)
                            {
                                string line = text.Substring(0, idxd);
                                line = Right("000" + line, 3);
                                if (line == POS)
                                    std += text.Substring(idxd + 1);
                            }
                        }
                    }
                    exAgent.SetCellValue(x, 25, std);
                    #endregion
                    if (positions < count)
                    {
                        exAgent.RangeCopy(x, x + 1, x + 2, x + 3);
                    }
                    
                    x = x + 2;
                }
                if (meeh.Trim() == "YRD")
                {
                    //VKKQTY = System.Math.Round(System.Convert.ToDouble(VKKQTY) * 0.9144, 0);
                }
                exAgent.SetCellValue(4, 35, positions + "个");
                FillFooter(x + 1, user);
//                src = dwVKKOPF["VKKSACHBE"].ToString();
   //             src = Helper.GetHRName(dsSource, src);
      //          exAgent.SetCellValue(x + 1, 6, src);
            }
            catch (Exception ex)
            {
                msg = "有错误产生，请注意:";
                EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.StopProgress);
                Tracer.Error(ex.StackTrace, ex);
                MessageBox.Show(msg + ex.Message);
            }
            finally
            {
                msg = "指令表虽然已经生产，但请注意核对。";
                EventDispatcher.Instance.RaiseStatus(msg, StatusEventType.StopProgress);
            }
        }
          
        public void FillFooter(int x,string user)
        {
            exAgent.SetCellValue(x, 21, "制单:");
            exAgent.SetCellValue(x, 24, user);
            exAgent.SetCellValue(x, 31, "审核:");
        }
        private void CheckDirectory(string root, string sub, string year, string month, string day)
        {
            try
            {
                string drlocal = root; 
                
                drlocal = root;
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                /*
                drlocal = Path.Combine(drlocal, "Sales");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                #endregion
                drlocal = Path.Combine(drlocal, sub);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

              drlocal = Path.Combine(drlocal, year);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);

                drlocal = Path.Combine(drlocal, month);
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                drlocal = Path.Combine(drlocal, "临时");
                if (!Directory.Exists(drlocal))
                    Directory.CreateDirectory(drlocal);
                */
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }
        private void ServerShareFile(string ordernumer, string fileType)
        {

            string destfile, tempfile = "";
            string fileabsname = "手织样-"+ordernumer ;
            try
            {
                filename = destfolder + "\\" + fileabsname + ".xls"; ;
                destfile = filename;
                if (File.Exists(fileabsname + ".xls"))
                {
                    File.Delete(fileabsname + ".xls");
                }
                tempfile = CreateFile(ordernumer);
                if (File.Exists(destfile))
                {
                    File.Delete(destfile);
                }
                File.Move(tempfile, destfile);

                if (fileType == "OpenExcel")
                {
                    System.Diagnostics.Process.Start("EXCEL.EXE", filename);
                }
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            finally
            {
                if (File.Exists(tempfile))
                {
                    File.Delete(tempfile);
                }
            }
        }
    }
}
